package _3_0dao;


import java.sql.*;
import java.util.*;

import _3_0.pojo.Admin;
import _3_0.utils.JDBCUtils;

public class AdminDAO {
	
	// DAO 负责提供增删改查的功能

	// 增加
	public void insert(Admin a) throws SQLException{
		
		String sql = "insert into admin values (?,?,md5(?))";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, a.getId());
		stmt.setString(2, a.getUsername());
		stmt.setString(3, a.getPassword());
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 删除
	public void deleteById(int id) throws SQLException{
		String sql = "delete from admin where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 修改
	public void update(Admin a) throws SQLException{
		
		String sql = "update admin set name = ? where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, a.getUsername());
		stmt.setInt(2, a.getId());
		// 3. 执行
		int result = stmt.executeUpdate();
		System.out.println("影响行数:"+result);
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
	};
	
	// 查询
	public Admin  findById(int id) throws SQLException{
		String sql = "select * from admin where id = ?";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		Admin a = null;
		if(rs.next())
		{
			String username = rs.getString(2);
			String password = rs.getString(3);
			a = new Admin(id,username,password);
		}
		
		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, null);
		
		return a;
	}
	
	public Admin find(String username,String password) throws SQLException 
	{
		String sql = "select * from admin where username = ? and password = md5(?)";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, username);
		stmt.setString(2, password);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		Admin c = null;
		if (rs.next()) {
			int id = rs.getInt(1);
			c = new Admin(id, username,password);
		}

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, rs);

		return c;
	}

	
	
	
	// 查询所有
	public List<Admin> findAll() throws SQLException {

		String sql = "select * from Admin";
		// 1. 得到连接
		Connection conn = JDBCUtils.getConnection();
		// 2. 创建查询对象
		PreparedStatement stmt = conn.prepareStatement(sql);
		// 3. 执行
		ResultSet rs = stmt.executeQuery();
		List<Admin> list = new ArrayList<>();
		while (rs.next()) {
			int id = rs.getInt(1);
			String name = rs.getString(2);
			String password = rs.getString(3);			
			Admin c = new Admin(id, name,password);
			list.add(c);
		}

		// 4. 释放资源
		JDBCUtils.destory(conn, stmt, rs);

		return list;

	};
}